<?php

	class Database {

		/** @var mysqli $connection */
		public $connection;
		public $persistent;
		public static $queries_count = 0;

		protected $db_host, $db_login, $db_password, $db_name;
		protected $charset = 'utf8';
		protected $config_name;
		protected static $instance = array();

		protected $tempTables = array();

		/**
		 * @param string $config_name
		 *
		 * @return null|Database
		 */
		public static function getInstance($config_name = 'main') {
			if (empty(self::$instance[$config_name])) {
				self::$instance[$config_name] = new Database($config_name);
			}

			return self::$instance[$config_name];
		}

		protected function __construct($config_name = 'main') {
			$this->config_name = $config_name;

			$config = 'Config_Database_' . $this->config_name;

			$this->db_host     = $config::$db_host;
			$this->db_login    = $config::$db_login;
			$this->db_password = $config::$db_password;
			$this->db_name     = $config::$db_name;
			$this->charset     = $config::$charset;
			$this->persistent  = property_exists($config, 'persistent') ? $config::$persistent : false;


			$this->connect();
		}

		public function connect() {

			$this->connection = new mysqli($this->db_host, $this->db_login, $this->db_password);
			if (!$this->connection) {
				throw new DatabaseException('NO CONNECTED TO ' . $this->db_host);
			}

			$this->query("SET NAMES '{$this->charset}'");
		}

		public function selectDb() {
			if (!$this->connection->select_db($this->db_name)) {
				throw new DatabaseException('DATABASE NOT SELECTED');
			}

			return true;
		}

		/**
		 * @param $sql
		 *
		 * @return bool|mysqli_result
		 * @throws DatabaseException
		 */
		public function query($sql) {
			if (!is_string($sql)) {
				throw new DatabaseException('Bad SQL query SQL:' . $sql, 0);
			}

			$this->selectDb();
			$result = $this->connection->query($sql);
			if ($code = $this->connection->errno) {
				throw new DatabaseException('Database error: ' . $this->connection->error . ' SQL:' . $sql, $code);
			}

			self::$queries_count++;

			return $result;
		}

		public function update($sql) {
			if ($this->query($sql)) {
				return $this->connection->affected_rows;
			} else {
				return false;
			}
		}

		public function insert($sql) {
			if ($this->query($sql)) {
				return $this->connection->insert_id;
			} else {
				return false;
			}
		}

		public function one($sql, $col = 0) {
			$q = $this->query($sql);
			if ($this->numRows($q)) {
				return $q->fetch_array(MYSQLI_NUM)[$col];
			} else {
				return null;
			}
		}

		public function fetch(mysqli_result $res) {
			$result = $res->fetch_assoc();

			if (is_array($result)) {
				foreach ($result as $k => $v) {
					if ((string)$v == (string)doubleval($v)) {
						$result[$k] = doubleval($v);
					} elseif ((string)$v == (string)intval($v)) {
						$result[$k] = intval($v);
					}
				}
			}

			return $result;
		}

		public function fetch_object(mysqli_result $res, $object_name) {
			$data = $res->fetch_assoc();
			if (empty($data)) return false;

			$obj = new $object_name;

			foreach ($obj as $k => $v) {

				if (!isset($data[$k])) continue;

				if (!is_null($v)) {
					if (is_array($v)) {
						$data[$k] = json_decode($data[$k], true);
					} else {
						settype($data[$k], gettype($v));
					}
				}
				$obj->$k = $data[$k];

			}

			return $obj;
		}

		public function fetch_query($sql) {
			$result = array();

			$q = $this->query($sql);
			if (!$q) {
				return false;
			}

			while ($row = $this->fetch($q)) {
				$result[] = $row;
			}

			return $result;
		}

		public function fetchColumn($sql) {
			$q      = $this->query($sql);
			$result = array();
			while ($row = $q->fetch_array(MYSQLI_NUM)) {
				$result[] = $row[0];
			}

			return $result;
		}

		public function numRows(mysqli_result $query) {
			return $query->num_rows;
		}

		public function escape($string) {
			return $this->connection->real_escape_string($string);
		}

		public function escapeArray($array) {
			if (!is_array($array)) {
				return false;
			}
			if (empty($array)) {
				return array();
			}

			$result = array();
			foreach ($array as $k => $v) {
				$result[$k] = is_array($v) ? $this->escapeArray($v) : $this->escape($v);
			}

			return $result;
		}

		public function optimizeTable($table) {
			$this->query("OPTIMIZE TABLE `{$table}`");
		}

		public function updateTable($table, $table_key, $data, $verbose = false) {

			if (empty($data) || empty($table_key) || empty($table)) {
				return;
			}
			if (!is_array($data)) {
				return;
			}

			$table     = $this->escape($table);
			$table_key = $this->escape($table_key);

			foreach ($data as $id => $info) {
				$id = $this->escape($id);

				$exec = false;
				$sql  = "UPDATE `{$table}` SET ";
				foreach ($info as $k => $v) {
					$k = $this->escape($k);
					if (is_null($v)) $v = 'NULL';

					$v = (int)$v === (string)$v ? (int)$v : ($v == 'NULL' ? $v : "'" . $this->escape($v) . "'");
					$sql .= " `{$k}`={$v},";
					$exec = true;
				}
				if ($exec) {
					$sql = mb_substr($sql, 0, -1) . " WHERE `{$table_key}`='{$id}'";
					if ($verbose) {
						echo $sql . "\n";
					}
					$this->query($sql);
				}
			}

		}

		public function updateTableSingle($table, $table_key, $data, $verbose = false) {

			if (empty($data) || empty($table_key) || empty($table) || !is_array($data)) {
				return;
			}

			$table     = $this->escape($table);
			$table_key = $this->escape($table_key);


			$id = $this->escape($data[$table_key]);
			unset($data[$table_key]);

			$sql = "UPDATE `{$table}` SET ";
			foreach ($data as $k => $v) {
				$k = $this->escape($k);
				$v = (int)$v === (string)$v ? (int)$v : $v == 'NULL' ? $v : "'" . $this->escape($v) . "'";
				$sql .= " `{$k}`={$v},";

			}

			$sql = mb_substr($sql, 0, -1) . " WHERE `{$table_key}`='{$id}'";
			if ($verbose) {
				echo $sql . "\n";
			}
			$this->query($sql);

		}

		public function insertTable($table, $data) {
			if (empty($data) || empty($table)) {
				return false;
			}

			if (isset($this->tempTables[$table])) {
				$table = $this->tempTables[$table]['name'];
			}

			$sql = "INSERT IGNORE INTO `{$table}` SET ";
			foreach ($data as $k => $v) {
				$k = $this->escape($k);
				$v = is_numeric($v) ? $v : $v == 'NULL' ? $v : "'" . $this->escape($v) . "'";
				$sql .= "`{$k}`={$v},";
			}
			$sql = mb_substr($sql, 0, -1);

			return $this->insert($sql);
		}

		public function insertUpdateMultiTable($table, $table_key, $data, $chunk_size = 100) {
			$keys = array_keys((array)current($data));

			$items   = array();
			$current = 0;
			$total   = count($data);

			foreach ($data as $row) {
				$items[] = $row;
				$current++;

				if ($current % $chunk_size == 0 || $current == $total) {
					$exec = false;
					$sql  = 'INSERT INTO `' . $table . '` (`' . implode('`,`', $keys) . '`) VALUES ';

					foreach ($items as $item) {
						$exec = true;
						$sql .= '(';
						foreach ($item as $field) {
							$field = $this->escape($field);
							$sql .= "'{$field}',";
						}
						$sql = mb_substr($sql, 0, -1);
						$sql .= '),';
					}

					if ($exec) {
						$sql = mb_substr($sql, 0, -1);
						$sql .= ' ON DUPLICATE KEY UPDATE ';
						foreach ($keys as $key) {
							if ($key == $table_key) continue;
							$sql .= " `{$key}` = VALUES(`{$key}`),";
						}
						$sql = mb_substr($sql, 0, -1);
						$this->query($sql);
					}
					$items = array();
				}

			}
		}

		public function insertUpdateTable($table, $key, $data) {
			if (empty($data) || empty($table) || empty($key)) {
				return false;
			}

			if (isset($this->tempTables[$table])) {
				$table = $this->tempTables[$table]['name'];
			}

			if (!is_array($key)) {
				$key = array($key);
			}

			$sql = "INSERT IGNORE INTO `{$table}` SET";
			foreach ($data as $k => $v) {
				$k = $this->escape($k);

				if (is_array($v) || is_object($v)) {
					$v = "'" . $this->escape(json_encode($v)) . "'";
				} else {
					$v = (int)$v === (string)$v ? (int)$v : $v == 'NULL' ? $v : "'" . $this->escape($v) . "'";
				}

				$sql .= "`{$k}`={$v},";
			}
			$sql = mb_substr($sql, 0, -1);

			$sql .= " ON DUPLICATE KEY UPDATE";
			foreach ($data as $k => $v) {
				if (in_array($k, $key)) {
					continue;
				}
				$k = $this->escape($k);
				$sql .= "`{$k}`=VALUES(`{$k}`),";
			}
			$sql = mb_substr($sql, 0, -1);

			return $this->insert($sql);
		}

		public function getFields($table) {
			$result = array();
			$q      = $this->query("SHOW COLUMNS FROM `{$table}`");
			while ($row = $this->fetch($q)) {
				$result[] = $row['Field'];
			}

			return $result;
		}

		public function getTables() {
			$result = array();

			if ($q = $this->query("SHOW TABLES")) {
				while ($row = $q->fetch_array(MYSQLI_NUM)) {
					$result[] = $row[0];
				}
			}

			return $result;
		}

		public function createTempTable($table, $copyData = true) {
			if (empty($table)) {
				return false;
			}

			$temp_table = "{$table}_temp";

//			$check_exists = $this->fetch($this->query("SHOW TABLES LIKE '{$temp_table}'"));
//			if (!empty($check_exists)){
//				throw new DatabaseException("Table `{$temp_table}` exists!");
//			}

			$this->query("DROP TABLE IF EXISTS `{$temp_table}`");
			$tmp          = $this->fetch($this->query("SHOW CREATE TABLE {$table}"));
			$create_table = $tmp['Create Table'];

			$tableEngine = false;
			if (strpos($create_table, 'MyISAM') !== false) {
				$tableEngine = 'MyISAM';
			}
			if (strpos($create_table, 'InnoDB') !== false) {
				$tableEngine = 'InnoDB';
			}

			if (!$tableEngine) {
				throw new AppException('Unknown table type');
			}

			$create_table = strtr($create_table, array(
				$tableEngine => 'MEMORY',
				"`{$table}`" => "`{$temp_table}`",
			));

			if ($copyData) {
				$this->query("{$create_table} (SELECT * FROM `{$table}`)");
			} else {
				$this->query($create_table);
			}

			$this->tempTables[$table]['engine'] = $tableEngine;
			$this->tempTables[$table]['name']   = $temp_table;

			return $temp_table;
		}

		public function releaseTempTable($table) {
			if (empty($table) || !isset($this->tempTables[$table])) {
				return false;
			}

			$this->query("OPTIMIZE TABLE `{$this->tempTables[$table]['name']}`");

			$result = $this->query("ALTER TABLE `{$this->tempTables[$table]['name']}` ENGINE = {$this->tempTables[$table]['engine']}")
				&& $this->query("RENAME TABLE `{$table}` TO `{$table}_back`")
				&& $this->query("RENAME TABLE `{$this->tempTables[$table]['name']}` TO `{$table}`")
				&& $this->query("DROP TABLE `{$table}_back`");

			if ($result) {
				unset($this->tempTables[$table]);
			}

			return $result;
		}
	}

	class DatabaseException extends Exception {
	}

